SQL Server-specific Geography Tasks
IN THIS PAGE
Description
SQL Server examples for creating and dropping tables with geometry columns, inserting location data values, inserting line data values, and inserting polygon data values.
In the samples below, :SRID indicates the optional spatial reference identifier argument. For SQL Server, the default is 4326.
Creating and Dropping Tables with Geometry Columns
Creating a table with geometry columns:
CREATE TABLE GeogTest ( KeyValue varchar(25) NOT NULL, Location Geography NOT NULL, PRIMARY KEY (KeyValue))
Dropping a table:
DROP TABLE GeogTest
Inserting Location Data Values
Inserting location data using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item1', GeogCreateLocation(-70, 42, :SRID))
Inserting location data using Native syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item1', geography::Point(42, - 70, :SRID))
Inserting Line Data Values
Inserting line data values using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item2', GeogCreateLine(-70, 42, -70, 38, :SRID))
Inserting line data values using Native SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item2', geography::STGeomFromText('LINESTRING(' + CAST( - 70 as Varchar(max)) + ' ' + CAST(42 as Varchar(max)) + ', ' + CAST( - 70 as Varchar(max)) + ' ' + CAST(38 as Varchar(max)) + ')', :SRID))
Inserting Polygon Data Values
Inserting polygon values using Portable SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) values('Item3', GeogCreatePolygon(-70, 42, -70, 32, -60, 32, -60, 42, -70, 42, :SRID))
Inserting polygon values using Native SQL Syntax:
INSERT INTO GeogTest (KeyValue, Location) VALUES ('Item3', geography::STGeomFromText('POLYGON((' + CAST( - 70 as Varchar(max)) + ' ' + CAST(42 as Varchar(max)) + ', ' + CAST( - 70 as Varchar(max)) + ' ' + CAST(32 as Varchar(max)) + ', ' + CAST( - 60 as Varchar(max)) + ' ' + CAST(32 as Varchar(max)) + ', ' + CAST( - 60 as Varchar(max)) + ' ' + CAST(42 as Varchar(max)) + ', ' + CAST( - 70 as Varchar(max)) + ' ' + CAST(42 as Varchar(max)) + '))', :SRID))
See Also
- Geographic Databases
- Changes to SQL Objects for Geometry
- Portable SQL Functions for Geographies
- Common Geography Database Tasks
- Database-specific Geography Tasks
- DB2-specific Geography Tasks
- MySQL-specific Geography Tasks
- Oracle-specific Geography Tasks
- PostgreSQL-specific Geography Tasks
- SQL Server-specific Geography Tasks
- SQL Geography Examples